﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using System.Data.SQLite;
using System.ComponentModel;
using BusinessObjects;
using System.Data;
using System.Drawing;
using Toolkit;
using System.Globalization;

namespace DataAccess {
  /// <summary>
  /// Data Access Layer for diary
  /// </summary>
  public class DiaryDal : IDiaryLogic {

    #region - Public methods -

    /// <summary>
    /// Gets the diary on date.
    /// </summary>
    /// <param name="date">The date.</param>
    /// <returns></returns>
    public BindingList<ChildDiary> GetDiaryOnDate( DateTime date ) {
      BindingList<ChildDiary> list = null;
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectOnDateCommand( connection, date ) ) {
          try {
            list = new BindingList<ChildDiary>( );
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                ChildDiary diary = new ChildDiary( );
                diary.Id = reader.GetValue( reader.GetOrdinal( "Id" ) ) == DBNull.Value ? 0 : reader.GetInt32( reader.GetOrdinal( "Id" ) );
                diary.ChildId = reader.GetInt32( reader.GetOrdinal( "ChildId" ) );
                diary.Name = reader.GetValue( reader.GetOrdinal( "ChildName" ) ).ToString( );
                diary.FirstName = reader.GetValue( reader.GetOrdinal( "ChildFirstName" ) ).ToString( );
                diary.ChildRemarks = reader.GetValue( reader.GetOrdinal( "ChildRemarks" ) ).ToString( );
                if ( reader.GetValue( reader.GetOrdinal( "ChildPhoto" ) ) != DBNull.Value ) {
                  byte[ ] photo = ( byte[ ] ) reader.GetValue( reader.GetOrdinal( "ChildPhoto" ) );
                  Image image = GraphicTools.Convert.ByteArrayToImage( photo );
                  if ( image != null ) {
                    diary.Photo = image;
                  }
                }
                diary.ChildDateIn = reader.GetValue( reader.GetOrdinal( "ChildDateIn" ) ) == DBNull.Value ? null : ( DateTime? ) reader.GetValue( reader.GetOrdinal( "ChildDateIn" ) );
                diary.ChildDateOut = reader.GetValue( reader.GetOrdinal( "ChildDateOut" ) ) == DBNull.Value ? null : ( DateTime? ) reader.GetValue( reader.GetOrdinal( "ChildDateOut" ) );

                DateTime arrivalDateTime = reader.GetValue( reader.GetOrdinal( "ArrivalDateTime" ) ) == DBNull.Value ? date.Date : ( DateTime ) reader.GetValue( reader.GetOrdinal( "ArrivalDateTime" ) );

                diary.Day = ( reader.GetValue( reader.GetOrdinal( "ArrivalDateTime" ) ) == DBNull.Value ? date : ( DateTime ) reader.GetValue( reader.GetOrdinal( "ArrivalDateTime" ) ) ).Date;

                diary.ArrivalTime = arrivalDateTime.ToShortTimeString( ).PadLeft( 5, '0' ) == "00:00" ? null : arrivalDateTime.ToShortTimeString( ).PadLeft( 5, '0' );
                DateTime departureDate = reader.GetValue( reader.GetOrdinal( "DepartureDateTime" ) ) == DBNull.Value ? date.Date : ( DateTime ) reader.GetValue( reader.GetOrdinal( "DepartureDateTime" ) );
                diary.DepartureTime = departureDate.ToShortTimeString( ).PadLeft( 5, '0' ) == "00:00" ? null : departureDate.ToShortTimeString( ).PadLeft( 5, '0' );
                diary.EatTime1 = reader.GetValue( reader.GetOrdinal( "EatTime1" ) ).ToString( );
                diary.EatTime2 = reader.GetValue( reader.GetOrdinal( "EatTime2" ) ).ToString( );
                diary.EatTime3 = reader.GetValue( reader.GetOrdinal( "EatTime3" ) ).ToString( );
                diary.EatComment = reader.GetValue( reader.GetOrdinal( "EatComment" ) ).ToString( );
                diary.SleepTimeFrom = reader.GetValue( reader.GetOrdinal( "SleepTimeFrom" ) ).ToString( );
                diary.SleepTimeTo = reader.GetValue( reader.GetOrdinal( "SleepTimeTo" ) ).ToString( );
                diary.SleepComment = reader.GetValue( reader.GetOrdinal( "SleepComment" ) ).ToString( );
                diary.ToiletComment = reader.GetValue( reader.GetOrdinal( "ToiletComment" ) ).ToString( );
                diary.DayComment = reader.GetValue( reader.GetOrdinal( "DayComment" ) ).ToString( );
                diary.NoteComment = reader.GetValue( reader.GetOrdinal( "NoteComment" ) ).ToString( );
                diary.MarkClean( );
                list.Add( diary );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
      return list;
    }

    /// <summary>
    /// Saves the specified diary.
    /// </summary>
    /// <param name="diary">The diary.</param>
    public void Save( BusinessObjects.ChildDiary diary ) {
      if ( diary.Id == 0 ) {
        Insert( diary );
      } else {
        Update( diary );
      }
    }

    /// <summary>
    /// Deletes the specified diary.
    /// </summary>
    /// <param name="diary">The diary.</param>
    public void Delete( ChildDiary diary ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          using ( SQLiteCommand command = CreateDeleteCommand( connection, diary.Id ) ) {
            command.Transaction = transaction;
            try {
              command.ExecuteNonQuery( );
              transaction.Commit( );
            } catch ( Exception ex ) {
              transaction.Rollback( );
              throw new DataException( ex.Message, ex );
            } finally {
              if ( connection.State != ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }
    }

    #endregion

    #region - Private methods -
    
    private void Insert( ChildDiary diary ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          SQLiteCommand command = CreateInsertCommand( connection, diary );
          command.Transaction = transaction;
          try {
            command.ExecuteNonQuery( );
            command = DataHelper.CreateSelectAfterInsertCommand( connection, "ChildrenDiaries" );
            diary.Id = Convert.ToInt32( command.ExecuteScalar( ), CultureInfo.CurrentCulture );



            transaction.Commit( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
    }

    private void Update( ChildDiary diary ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          using ( SQLiteCommand command = CreateUpdateCommand( connection, diary ) ) {
            command.Transaction = transaction;
            try {
              command.ExecuteNonQuery( );
              transaction.Commit( );
              diary.MarkClean( );
            } catch ( Exception ex ) {
              transaction.Rollback( );
              throw new DataException( ex.Message, ex );
            } finally {
              if ( connection.State != ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }
    }

    #region - SQL statements -

    private SQLiteCommand CreateInsertCommand( SQLiteConnection connection, ChildDiary diary ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO ChildrenDiaries (ChildId, ArrivalDateTime, DepartureDateTime,
                              EatTime1, EatTime2, EatTime3, EatComment, SleepTimeFrom, SleepTimeTo, SleepComment,
                              ToiletComment, DayComment, NoteComment)
                              VALUES (@childid, @arrivaldatetime, @departuredatetime, 
                              @eattime1, @eattime2, @eattime3, @eatcomment, @sleeptimefrom, @sleeptimeto, @sleepcomment,
                              @toiletcomment, @daycomment, @notecomment)";
      command.Parameters.AddWithValue( "@childid", diary.ChildId );
      command.Parameters.AddWithValue( "@arrivaldatetime", diary.ArrivalDateTime );
      command.Parameters.AddWithValue( "@departuredatetime", diary.DepartureDateTime );
      command.Parameters.AddWithValue( "@eattime1", diary.EatTime1 );
      command.Parameters.AddWithValue( "@eattime2", diary.EatTime2 );
      command.Parameters.AddWithValue( "@eattime3", diary.EatTime3 );
      command.Parameters.AddWithValue( "@eatcomment", diary.EatComment );
      command.Parameters.AddWithValue( "@sleeptimefrom", diary.SleepTimeFrom );
      command.Parameters.AddWithValue( "@sleeptimeto", diary.SleepTimeTo );
      command.Parameters.AddWithValue( "@sleepcomment", diary.SleepComment );
      command.Parameters.AddWithValue( "@toiletcomment", diary.ToiletComment );
      command.Parameters.AddWithValue( "@daycomment", diary.DayComment );
      command.Parameters.AddWithValue( "@notecomment", diary.NoteComment );

      return command;
    }

    private SQLiteCommand CreateUpdateCommand( SQLiteConnection connection, ChildDiary diary ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE ChildrenDiaries SET ChildId = @childid,
                              ArrivalDateTime = @arrivaldatetime, DepartureDateTime = @departuredatetime,
                              EatTime1 = @eattime1, EatTime2 = @eattime2, EatTime3 = @eattime3, EatComment = @eatcomment, 
                              SleepTimeFrom = @sleeptimefrom, SleepTimeTo = @sleeptimeto, SleepComment = @sleepcomment, 
                              ToiletComment = @toiletcomment, DayComment = @daycomment, NoteComment = @notecomment
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@childid", diary.ChildId );
      command.Parameters.AddWithValue( "@arrivaldatetime", diary.ArrivalDateTime );
      command.Parameters.AddWithValue( "@departuredatetime", diary.DepartureDateTime );
      command.Parameters.AddWithValue( "@eattime1", diary.EatTime1 );
      command.Parameters.AddWithValue( "@eattime2", diary.EatTime2 );
      command.Parameters.AddWithValue( "@eattime3", diary.EatTime3 );
      command.Parameters.AddWithValue( "@eatcomment", diary.EatComment );
      command.Parameters.AddWithValue( "@sleeptimefrom", diary.SleepTimeFrom );
      command.Parameters.AddWithValue( "@sleeptimeto", diary.SleepTimeTo );
      command.Parameters.AddWithValue( "@sleepcomment", diary.SleepComment );
      command.Parameters.AddWithValue( "@toiletcomment", diary.ToiletComment );
      command.Parameters.AddWithValue( "@daycomment", diary.DayComment );
      command.Parameters.AddWithValue( "@notecomment", diary.NoteComment );
      command.Parameters.AddWithValue( "@id", diary.Id );

      return command;
    }

    private SQLiteCommand CreateUpdateChildStateCommand( SQLiteConnection connection, int childId, byte state, byte activeState ) {
      if ( childId == 0 ) throw new ArgumentException( "Unable to update empty Child ID." );

      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE Children SET State = @state, ActiveState = @activestate
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@state", state );
      command.Parameters.AddWithValue( "@activestate", activeState );
      command.Parameters.AddWithValue( "@id", childId );

      return command;
    }

    private SQLiteCommand CreateDeleteCommand( SQLiteConnection connection, int diaryId ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"DELETE FROM ChildrenDiaries WHERE ID = @id";
      command.Parameters.AddWithValue( "@id", diaryId );
      return command;
    }

    private SQLiteCommand CreateSelectOnDateCommand( SQLiteConnection connection, DateTime date ) {

      DateTime date1 = date.Date;
      DateTime date2 = date1.AddDays( 1 );

      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT Children.[ID] AS ChildId,
                              Children.[Name] AS ChildName, Children.[FirstName] AS ChildFirstName,     
                              Children.DateIn AS ChildDateIn, Children.DateOut AS ChildDateOut,  
                              Children.[Photo] AS ChildPhoto, Children.[ActiveState] AS ChildState,
                              Children.[Remarks] AS ChildRemarks,
                              ChildrenDiaries.ID AS Id,
                              ChildrenDiaries.[ArrivalDateTime] AS ArrivalDateTime,       
                              ChildrenDiaries.[DepartureDateTime] AS DepartureDateTime,       
                              ChildrenDiaries.[EatTime1] AS EatTime1,          
                              ChildrenDiaries.[EatTime2] AS EatTime2,          
                              ChildrenDiaries.[EatTime3] AS EatTime3,       
                              ChildrenDiaries.[EatComment] AS EatComment,       
                              ChildrenDiaries.[SleepTimeFrom] AS SleepTimeFrom,    
                              ChildrenDiaries.[SleepTimeTo] AS SleepTimeTo,       
                              ChildrenDiaries.[SleepComment] AS SleepComment,       
                              ChildrenDiaries.[ToiletComment] AS ToiletComment,       
                              ChildrenDiaries.[DayComment] AS DayComment,       
                              ChildrenDiaries.[NoteComment] AS NoteComment   
                         FROM Children LEFT OUTER JOIN ChildrenDiaries on Children.ID = ChildrenDiaries.ChildId
                              AND ChildrenDiaries.[ArrivalDateTime] between @date1 and @date2
                         WHERE Children.state = 0";
      command.Parameters.AddWithValue( "@date1", date1 );
      command.Parameters.AddWithValue( "@date2", date2 );
      return command;
    }

    #endregion

    #endregion

  }
}
